package com.jd.service.impl;

import com.common.model.Result;
import com.common.utils.AccountContextHolder;
import com.gavel.parser.MYMROSkuParser;
import com.gavel.parser.SkuItem;
import com.gavel.parser.SkuParser;
import com.gavel.utils.StringUtils;
import com.google.gson.Gson;
import com.jd.model.VCProductInfoCondition;
import com.jd.model.VCProductInfoVO;
import com.jd.service.JDProductInfoService;
import com.jingsu.ImageLoader;
import com.jingsu.MymroInpaint;
import com.mrobuy.jd.JdClientFactory;
import com.mrobuy.jd.api.Jos;
import com.mrobuy.jdvc.dao.JDAccountDao;
import com.mrobuy.jdvc.dao.VCItemDao;
import com.mrobuy.jdvc.dao.VCProductDao;
import com.mrobuy.jdvc.dao.VCProductInfoDao;
import com.mrobuy.jdvc.entity.*;
import com.mrobuy.jdvc.service.HtmlLodService;
import com.mrobuy.util.CodeEncoder;
import com.mrobuy.util.ExcelUtils;
import com.mrobuy.util.HttpUtils;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;

import javax.servlet.ServletOutputStream;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;

@Service("jdProductInfoService")
public class JDProductInfoServiceImpl implements JDProductInfoService {

    private static Logger LOG = LoggerFactory.getLogger(JDProductInfoServiceImpl.class);

    private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy/MM/dd");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private JDAccountDao jdAccountDao;

    @Autowired
    private VCProductInfoDao vcProductInfoDao;

    @Autowired
    private VCItemDao vcItemDao;

    @Autowired
    private HtmlLodService htmlLodService;

    @Autowired
    private VCProductDao vcProductDao;

    @Value("${image.dir}")
    private String imageDir;

    @Value("${image.original.dir}")
    private String originalImageDir;

    @Override
    public Result<VCProductInfoVO> loadProductInfos(VCProductInfoCondition condition) {

        Result<VCProductInfoVO> result = new Result();

        List<Object> params = new ArrayList<>();

        StringBuilder whereCond = new StringBuilder(" where 1 =1 ");
        if ( condition.getSaleState() != null ) {
            whereCond.append("  and SALESTATE = ? ");
            params.add(condition.getSaleState());
        }

        if ( condition.getIsPrimary() != null ) {
            whereCond.append("  and ISPRIMARY = ? ");
            params.add(condition.getIsPrimary());
        }

        if ( StringUtils.isNotBlank(condition.getVendorCode()) ) {
            whereCond.append("  and VENDORCODE = ? ");
            params.add(condition.getVendorCode());
        }

        if ( condition.getStart() != null ) {
            whereCond.append("  and MODIFYTIME >= ? ");
            params.add(condition.getStart());
        }

        if ( condition.getPublishexport() != null ) {
            whereCond.append("  and  IFNULL(PUBLISHEXPORT, 'N') =  ? ");
            params.add(condition.getPublishexport());
        }

        if ( condition.getEpexport() != null ) {
            whereCond.append("  and  IFNULL(EPEXPORT, 'N') =  ? ");
            params.add(condition.getEpexport());
        }

        if ( condition.getEnd() != null ) {
            whereCond.append("  and MODIFYTIME <= ? ");
            params.add(condition.getEnd());
        }

        if (StringUtils.isNotBlank(condition.getWareId()) ) {
            whereCond.append("  and WAREID = ? ");
            params.add(StringUtils.trim(condition.getWareId()));
        }

        if (StringUtils.isNotBlank(condition.getWareName()) ) {
            whereCond.append("  and WARENAME like ? ");
            params.add("%" + StringUtils.trim(condition.getWareName()) + "%");
        }

        try {
            String sql = "select * from VC_PRODUCT_INFO ";

            String cnt_sql = "select count(1)  from VC_PRODUCT_INFO ";

            int count = jdbcTemplate.queryForObject(cnt_sql + whereCond.toString(), params.toArray(new Object[params.size()]), Integer.class);


            int fromIndex = (condition.getPage() - 1)*condition.getLimit();
            int toIndex = condition.getPage()*condition.getLimit();

            if ( fromIndex > count ) {
                fromIndex = 0;
                toIndex = 0;
            } else if ( toIndex > count ) {
                toIndex = count;
            }

            params.add(fromIndex);
            params.add(toIndex);
            List<VCProductInfoVO> list = jdbcTemplate.query(sql + whereCond.toString() + " limit ?, ?", params.toArray(new Object[params.size()]), new BeanPropertyRowMapper(VCProductInfoVO.class));

            result.setCode(0);
            result.setCount(count);
            result.setData(list);

        } catch (Exception e) {
            LOG.error(e.getMessage());
        }

        return result;

    }

    @Override
    public void downloadPics(ServletOutputStream outputStream) {

        JDAccount account = jdAccountDao.loadByVendor("wysgyp");

        List<VCProductInfo> productInfos = vcProductInfoDao.list(account.getVccode(), 0);
        
        if ( productInfos!=null ) {

            try ( ZipOutputStream zipOut = new ZipOutputStream(outputStream ) ){

                zipOut.putNextEntry(new ZipEntry( "lucency/"));
                zipOut.closeEntry();

                zipOut.putNextEntry(new ZipEntry("pic/"));
                zipOut.closeEntry();

                for (VCProductInfo productInfo : productInfos) {
                    // 如果没有提交过主图申请, 就提交主图申请
                    if (StringUtils.isBlank(productInfo.getPicApplyId())) {

                        VCProduct product = loadProduct(productInfo.getWareId());
                        if ( product==null ) {
                            System.out.println("没有商品Product信息！");
                            continue;
                        }

                        List<VCItem> vcItems = product.getVcItems();
                        if ( vcItems==null || vcItems.size()==0 ) {
                            vcItems = vcItemDao.loadByWareId(productInfo.getWareId());
                        }

                        if ( vcItems==null || vcItems.size()==0 ) {
                            System.out.println("没有Item商品信息！");
                            continue;
                        }

                        for (VCItem vcItem : vcItems) {
                            System.out.println(vcItem.getSkuName() + ":" + vcItem.getItemNum() + " ==> " + CodeEncoder.decoder(vcItem.getItemNum()));
                        }

                        int i = 0;
                        for (VCItem vcItem : vcItems) {
                            i++;
                            System.out.println("\t" + i + ". [" + vcItem.getSkuName() + "][" + vcItem.getSkuId() + "]: " + vcItem.getItemNum());

                            String code = CodeEncoder.decoder(vcItem.getItemNum());

                            SkuParser skuParser = new MYMROSkuParser();
                            String url = String.format("https://www.mymro.cn/u-%s.html", code);
                            String html = htmlLodService.load(url);
                            SkuItem skuItem = skuParser.parser(url, html);
                            if ( skuItem==null ) {
                                System.out.println("[" + code + ": " + vcItem.getSkuName() + "]SKU信息解析失败.");
                                continue;
                            }

                            boolean lucency = false;
                            for (SkuItem.Pic pic : skuItem.getProductPics()) {
                                System.out.println("\t" + pic.getPos() + ". " + pic.getUrl() + "\n");

                                try {
                                    // 如果没有 生成无水印图片
                                    File watermarkFreePicture = new File(imageDir, ImageLoader.getPath(pic.getUrl()));
                                    if ( !watermarkFreePicture.exists() ) {
                                        if ( StringUtils.isBlank(originalImageDir) ) {
                                            originalImageDir = imageDir + File.separator + "original";
                                        }

                                        File originalPicture = new File(originalImageDir, ImageLoader.getPath(pic.getUrl()));
                                        if ( !originalPicture.exists() ) {
                                            HttpUtils.download(pic.getUrl(), originalPicture.getAbsolutePath());
                                        }

                                        if ( originalPicture.exists() ) {
                                            MymroInpaint.inpaint(originalPicture.getAbsolutePath(), watermarkFreePicture.getAbsolutePath());
                                        } else {
                                            System.out.println("[" + pic.getUrl() + "]文件下载失败");
                                        }
                                    }

                                    if ( !watermarkFreePicture.exists() ) {
                                        System.out.println("[" + watermarkFreePicture + "]文件处理失败");
                                        continue;
                                    }




                                    String filename = "MYMRO_" + code + "_" + pic.getPos() + "."  + com.google.common.io.Files.getFileExtension(watermarkFreePicture.getName());


                                    {
                                        FileInputStream fis = new FileInputStream(watermarkFreePicture);
                                        ZipEntry zipEntry = new ZipEntry("pic/" + filename);
                                        zipOut.putNextEntry(zipEntry);
                                        byte[] bytes = new byte[1024];
                                        int length;
                                        while((length = fis.read(bytes)) >= 0) {
                                            zipOut.write(bytes, 0, length);
                                        }
                                        fis.close();
                                    }

                                    if ( !lucency ) {

                                        FileInputStream fis = new FileInputStream(watermarkFreePicture);
                                        ZipEntry zipEntry = new ZipEntry("lucency/" + filename);
                                        zipOut.putNextEntry(zipEntry);
                                        byte[] bytes = new byte[1024];
                                        int length;
                                        while((length = fis.read(bytes)) >= 0) {
                                            zipOut.write(bytes, 0, length);
                                        }
                                        fis.close();

                                        lucency = true;
                                    }
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }

    }

    @Override
    public void uploadFile(InputStream inputStream) throws IOException {

        ZipInputStream zis = new ZipInputStream(inputStream);

        ZipEntry zipEntry = zis.getNextEntry();
        File dir = new File(imageDir + File.separator +  "upload");
        if ( !dir.exists() ) {
            dir.mkdirs();
        }

        byte[] buffer = new byte[1024];

        while (zipEntry != null) {
            File newFile = newFile(dir, zipEntry);
            if ( newFile!=null ) {
                if (zipEntry.isDirectory()) {
                    if (!newFile.isDirectory() && !newFile.mkdirs()) {
                        throw new IOException("Failed to create directory " + newFile);
                    }
                } else {
                    File parent = newFile.getParentFile();
                    if (!parent.isDirectory() && !parent.mkdirs()) {
                        throw new IOException("Failed to create directory " + parent);
                    }

                    // write file content
                    FileOutputStream fos = new FileOutputStream(newFile);
                    int len;
                    while ((len = zis.read(buffer)) > 0) {
                        fos.write(buffer, 0, len);
                    }
                    fos.close();
                }
            }

            zipEntry = zis.getNextEntry();
        }


        zis.closeEntry();
        zis.close();

    }

    @Override
    public void publishexport(ServletOutputStream outputStream) {

        VCProductInfoCondition condition = new VCProductInfoCondition();
        condition.setSaleState(2);
        condition.setPublishexport("N");
        condition.setVendorCode(AccountContextHolder.vendorCode());
        Result<VCProductInfoVO> vcProductInfoVOResult =  loadProductInfos(condition);

        try {
            InputStream fis = getClass().getClassLoader().getResourceAsStream("template/publish_template.xlsx");
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            fis.close();

            XSSFSheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
            if ( vcProductInfoVOResult!=null && vcProductInfoVOResult.getData() != null ) {
                int rowIndex = 1;
                List<VCProductInfoVO> vcProductInfos = vcProductInfoVOResult.getData();
                for (VCProductInfoVO vcProductInfoVO : vcProductInfos) {
                    try {
                        // 提报日期	采购员	销售员	商品编号	商品名称	供应商简码	供应商名称	起订量	是否厂直	是否拆单	是否前台隐藏	商详合规审核状态	商品状态
                        // 2021/03/03	luokangjian 罗康健	panyuzhu 潘昱竹	100018867058	京苏 BST0951 OSHA警告标识（150*200mm,ABS,中英文） 【预计14天出货】	jsyg	京苏易购电子商务（江苏）有限公司	3	否	否	否	审核通过	可上柜
                        // A        B       C       D           E           F           G           H       I           J           K               L                   M
                        // 0        1       2       3           4           5           6           7       8           9           10              11                  12

                        VCProduct vcProduct = loadProduct(vcProductInfoVO.getWareId());

                        ExcelUtils.setCellValue(sheet, rowIndex, 0, DATE_FORMAT.format(Calendar.getInstance().getTime()));   // 提报日期
                        ExcelUtils.setCellValue(sheet, rowIndex, 1, vcProduct.getPurchasercode() + " " + vcProduct.getPurchaserName());   // 采购员
                        ExcelUtils.setCellValue(sheet, rowIndex, 2, vcProduct.getSalercode() + " " + vcProduct.getSalerName());   // 销售员
                        ExcelUtils.setCellValue(sheet, rowIndex, 3, vcProduct.getWareId());    // 商品编号
                        ExcelUtils.setCellValue(sheet, rowIndex, 4, vcProduct.getName());       // 商品名称
                        ExcelUtils.setCellValue(sheet, rowIndex, 5, vcProduct.getVendorCode());   // 供应商简码
                        ExcelUtils.setCellValue(sheet, rowIndex, 6, vcProduct.getVendorName());        // 供应商名称
                        ExcelUtils.setCellValue(sheet, rowIndex, 7, "1");        // 起订量
                        ExcelUtils.setCellValue(sheet, rowIndex, 8, "否");        // 是否厂直
                        ExcelUtils.setCellValue(sheet, rowIndex, 9, "否");        // 是否拆单
                        ExcelUtils.setCellValue(sheet, rowIndex, 10, "否");        // 是否前台隐藏
                        ExcelUtils.setCellValue(sheet, rowIndex, 11, "审核通过");        // 商详合规审核状态
                        ExcelUtils.setCellValue(sheet, rowIndex, 12, "可上柜");        // 商品状态


                    } catch (Exception e) {
                        continue;
                    }

                    for (int i = 0; i < 13; i++) {
                        ExcelUtils.applyCellStyle(sheet, rowIndex, i, ExcelUtils.getCellStyle(sheet, 1, i));
                    }

                    rowIndex++;

                    VCProductInfo vcProductInfo = new VCProductInfo();
                    vcProductInfo.setWareId(vcProductInfoVO.getWareId());
                    vcProductInfo.setPublishexport("Y");
                    vcProductInfo.setPublishexporttime(Calendar.getInstance().getTime());
                    vcProductInfoDao.update(vcProductInfo, false);
                }


                XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
                formulaEvaluator.evaluateAll();


                // 结束吧活动cell定位在excel的第一列
                ExcelUtils.setCellValue(sheet, rowIndex, 0, "");
            }


            wb.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public void epexport(ServletOutputStream outputStream) {
        VCProductInfoCondition condition = new VCProductInfoCondition();
        condition.setSaleState(2);
        condition.setEpexport("N");
        condition.setVendorCode(AccountContextHolder.vendorCode());
        Result<VCProductInfoVO> vcProductInfoVOResult =  loadProductInfos(condition);

        try {

            InputStream fis = getClass().getClassLoader().getResourceAsStream("template/ep_template.xlsx");
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            fis.close();

            XSSFSheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
            if ( vcProductInfoVOResult!=null && vcProductInfoVOResult.getData() != null ) {
                int rowIndex = 1;
                List<VCProductInfoVO> vcProductInfos = vcProductInfoVOResult.getData();
                for (VCProductInfoVO vcProductInfoVO : vcProductInfos) {

                    List<VCItem>  vcItems = vcItemDao.loadByWareId(vcProductInfoVO.getWareId());
                    if ( vcItems!=null && vcItems.size() > 0 ) {

                        for (VCItem vcItem : vcItems) {

                            try {

                                // 提报日期	    一级分类	二级分类	三级分类	    品牌	SKU	            商品名称	                                                协议价或者京东价	商品属性	适用客户（通用/专供）
                                // 2021/03/03	工业品	    安防用品	安全警示标识	京苏	100018865172	京苏 BST0692 灭火设备标识（150*200mm,ABS） 【预计14天出货】	42.15	            自营	    通用
                                // A            B           C           D               E       F               G                                                           H                   I           J
                                // 0            1           2           3               4       5               6                                                           7                   8           9

                                VCProduct vcProduct = loadProduct(vcProductInfoVO.getWareId());

                                String categoryName = vcProductInfoVO.getCategoryName();
                                // 工业品---手动工具---扭力类工具
                                String[] categories = categoryName.split("---");

                                ExcelUtils.setCellValue(sheet, rowIndex, 0, DATE_FORMAT.format(Calendar.getInstance().getTime()));   // 提报日期
                                ExcelUtils.setCellValue(sheet, rowIndex, 1, categories[0]);   // 一级分类
                                ExcelUtils.setCellValue(sheet, rowIndex, 2, categories[1]);   // 二级分类
                                ExcelUtils.setCellValue(sheet, rowIndex, 3, vcProduct.getCidName1());    // 三级分类
                                ExcelUtils.setCellValue(sheet, rowIndex, 4, vcProduct.getBrandName());       // 品牌
                                ExcelUtils.setCellValue(sheet, rowIndex, 5, vcItem.getSkuId());   // SKU
                                ExcelUtils.setCellValue(sheet, rowIndex, 6, vcItem.getSkuName());        // 商品名称

                                ExcelUtils.setCellValue(sheet, rowIndex, 7, vcItem.getMemberPrice());        // 协议价或者京东价
                                ExcelUtils.setCellValue(sheet, rowIndex, 8, "自营");        // 商品属性
                                ExcelUtils.setCellValue(sheet, rowIndex, 9, "通用");        // 适用客户（通用/专供）

                                for (int i = 0; i < 10; i++) {
                                    ExcelUtils.applyCellStyle(sheet, rowIndex, i, ExcelUtils.getCellStyle(sheet, 1, i));
                                }

                                rowIndex++;

                                VCProductInfo vcProductInfo = new VCProductInfo();
                                vcProductInfo.setWareId(vcProductInfoVO.getWareId());
                                vcProductInfo.setEpexport("Y");
                                vcProductInfo.setEpexporttime(Calendar.getInstance().getTime());
                                vcProductInfoDao.update(vcProductInfo, false);

                            } catch (Exception e) {
                                continue;
                            }

                        }
                    }

                }

                // 结束吧活动cell定位在excel的第一列
                // ExcelUtils.setCellValue(sheet, rowIndex, 0, "");
            }


            wb.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static File newFile(File destinationDir, ZipEntry zipEntry) throws IOException {

        String path = zipEntry.getName();
        while ( true ) {
            if (  StringUtils.isBlank(path) ) {
               return null;
            }
            if ( path.startsWith("lucency/") ) {
                break;
            }
            if (path.startsWith("pic/") ) {
                break;
            }

            path = path.substring(path.indexOf("/") + 1);

        }

        File destFile = new File(destinationDir, path);

        String destDirPath = destinationDir.getCanonicalPath();
        String destFilePath = destFile.getCanonicalPath();

        if (!destFilePath.startsWith(destDirPath + File.separator)) {
            throw new IOException("Entry is outside of the target dir: " + zipEntry.getName());
        }

        return destFile;
    }


    private VCProduct loadProduct(String wareId){

        VCProduct vcProduct =  vcProductDao.loadWareId(wareId);
        if ( vcProduct!=null ) {
            return vcProduct;
        }

        try {
            vcProduct = Jos.itemProductGetClient(JdClientFactory.jdClient())
                    .setWareId(wareId)
                    .call();

            if ( vcProduct!=null ) {
                vcProductDao.insert(vcProduct);
                if (  vcProduct.getVcItems()!=null ) {
                    for (VCItem vcItem : vcProduct.getVcItems()) {
                        vcItemDao.insert(vcItem);
                    }
                }
            }

            return vcProduct;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

}
